ALTER TABLE [dbo].[project]
ADD [budgetoverrideamount] numeric(15,2) NULL ,
[budgetoverrideperiodstartdate] datetime NULL ,
[budgetoverrideperiodenddate] datetime NULL 
GO

CREATE TRIGGER [paymentrequestlineitemInsertBudgetOverride]
ON [paymentrequestlineitem]
AFTER INSERT, UPDATE, DELETE
AS
begin
DECLARE @projectid int;
DECLARE @createdate datetime;
DECLARE @totalfunds numeric(15,2);
DECLARE @totalamount numeric(15,2);
DECLARE @overrideamount numeric(15,2);
DECLARE @overrideperiodstartdate datetime;
DECLARE @overrideperiodenddate datetime;
	
IF EXISTS (SELECT *FROM INSERTED I JOIN DELETED D ON I.id = D.id WHERE I.paymentrequestamount IS NOT NULL
	/*AND (D.paymentrequestamount IS NULL OR I.paymentrequestamount <> D.paymentrequestamount)*/
	OR D.paymentrequestamount IS NOT NULL)
	begin
		select @projectid=projectid,@createdate=createdate 
		from INSERTED;

		SELECT @totalfunds=(ISNULL(project.managementfunds,0) + ISNULL(project.partnergrantfunds, 0)),@totalamount=(SELECT SUM(ISNULL(paymentrequestamount, 0)) FROM paymentrequestlineitem payment WHERE payment.projectid = project.id),@overrideamount=budgetoverrideamount,@overrideperiodstartdate=budgetoverrideperiodstartdate,@overrideperiodenddate=budgetoverrideperiodenddate FROM project project WHERE project.id=@projectid;

		IF @totalamount>@totalfunds AND ((@totalamount - @totalfunds)>@overrideamount
			OR (@createdate IS NOT NULL
				AND (@overrideperiodstartdate IS NOT NULL AND @createdate<@overrideperiodstartdate
					OR @overrideperiodenddate IS NOT NULL AND @createdate>@overrideperiodenddate)))
			begin
				update project set budgetoverride=null where project.id=@projectid;
			end
		;

	end

end;


GO